Imports System.Data.OleDb
Imports DiceShenanigensUserControls

Module Database

   ' Stores the username for the game
   Public Username As String = ""

   ' Size of the grid
   Public gameGridSize As GridSize = GridSize.Small

   ' Database Registry key
   Friend dbkey As Microsoft.Win32.RegistryKey

   ' Location of the Database
   Public DBLocation As String

   ' Connection String
   Public Const connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

   ' One connection to one database file
   Public dbConn As New OleDbConnection

   ''' <summary>
   ''' Makes the connection. Stores the location in registry.
   ''' </summary>
   ''' <remarks></remarks>
   Public Function makeConnection() As Boolean

      Try

         dbkey = Microsoft.Win32.Registry.LocalMachine.CreateSubKey("Software\UWPCS3340S2007")
         DBLocation = dbkey.GetValue("Software\UWPCS3340S2007", "")

         dbConn.ConnectionString = connString & DBLocation

         dbConn.Open()
         dbConn.Close()

         Return True

      Catch ex As Exception

         'MsgBox(ex.Message)
         openDialog()
         dbkey.SetValue("Software\UWPCS3340S2007", DBLocation, Microsoft.Win32.RegistryValueKind.String)
         dbConn.ConnectionString = connString & DBLocation

      End Try

      Return False

   End Function

   ''' <summary>
   ''' Sets up all the tables.
   ''' </summary>
   ''' <remarks></remarks>
   Friend Sub setUpTable()

      Dim connected As Boolean = False

      Dim myCommand As New OleDbCommand

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "SELECT UID FROM Users "
      End With

      While connected = False

         Try

            dbConn.Open()
            myCommand.ExecuteReader().Read()
            connected = True
            dbConn.Close()

         Catch ex As Exception

            If dbConn.State = ConnectionState.Open Then
               dbConn.Close()
            End If

            openDialog()
            dbConn.ConnectionString = connString & DBLocation
            dbkey.SetValue("Software\UWPCS3340S2007", DBLocation, Microsoft.Win32.RegistryValueKind.String)

         End Try

      End While

   End Sub

   ''' <summary>
   ''' Opens the file dialog.
   ''' </summary>
   ''' <remarks></remarks>
   Private Sub openDialog()

      Dim openDB As New OpenFileDialog
      openDB.Filter = "MS Access Database (*.mdb)|*.mdb"

      If openDB.ShowDialog() = Windows.Forms.DialogResult.OK Then

         DBLocation = openDB.FileName

      Else

         MsgBox("No Database Selected!")
         End
         Application.Exit()

      End If

   End Sub

   ''' <summary>
   ''' Checks to see if the user exists in the database.
   ''' </summary>
   ''' <param name="username">Username of the user</param>
   ''' <param name="password">Password of the user</param>
   ''' <returns>True if found; False otherwise</returns>
   ''' <remarks></remarks>
   Public Function checkUser(ByVal username As String, ByVal password As String) As Boolean

      Dim myCommand As New OleDbCommand

      Try
         dbConn.Open()
      Catch ex As Exception
         makeConnection()
         dbConn.Open()
      End Try

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "SELECT UID FROM Users " _
                        & "WHERE (Username = '" & username & "') AND ([Password] = '" & password & "')"
      End With

      Dim contains As Boolean = myCommand.ExecuteReader.Read

      dbConn.Close()
      myCommand.Dispose()

      Return contains

   End Function

   ''' <summary>
   ''' 
   ''' </summary>
   ''' <param name="username"></param>
   ''' <returns></returns>
   ''' <remarks></remarks>
   Public Function getUserData(ByVal username As String) As User

      Dim usr As User = Nothing
      Dim myCommand As New OleDbCommand
      Dim myReader As OleDbDataReader = Nothing

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "SELECT UID, [First Name], [Last Name], [Games Played], Wins, Losses " _
                        & "FROM Users WHERE (Username = '" & username & "')"
      End With

      Try
         dbConn.Open()
      Catch ex As Exception
         makeConnection()
         dbConn.Open()
      End Try

      Try

         myReader = myCommand.ExecuteReader
         myReader.Read()

         usr.UID = myReader(0)
         usr.FirstName = myReader(1)
         usr.LastName = myReader(2)
         usr.GamesPlayed = myReader(3)
         usr.Wins = myReader(4)
         usr.Losses = myReader(5)

      Catch Exp As OleDb.OleDbException

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "Oledb Error")

      Catch Exp As Exception

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")

      End Try

      dbConn.Close()
      myCommand.Dispose()
      myReader.Close()

      Return usr

   End Function

   ''' <summary>
   ''' 
   ''' </summary>
   ''' <param name="username"></param>
   ''' <param name="password"></param>
   ''' <param name="first"></param>
   ''' <param name="last"></param>
   ''' <returns></returns>
   ''' <remarks></remarks>
   Public Function createUser(ByVal username As String, ByVal password As String, ByVal first As String, ByVal last As String) As Boolean

      Dim myCommand As New OleDbCommand

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "INSERT INTO Users (Username, [Password], [First Name], [Last Name]) VALUES('" & username & "', '" & password & "', '" & first & "', '" & last & "')"
      End With

      Try
         dbConn.Open()
      Catch ex As Exception
         makeConnection()
         dbConn.Open()
      End Try

      Try

         myCommand.ExecuteNonQuery()
         dbConn.Close()
         myCommand.Dispose()

      Catch Exp As OleDb.OleDbException

         MsgBox(Exp.GetType, MsgBoxStyle.Critical, "Oledb Error")
         Return False

      Catch Exp As Exception

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
         Return False

      End Try

      Return True

   End Function

   ''' <summary>
   ''' 
   ''' </summary>
   ''' <param name="owners"></param>
   ''' <param name="values"></param>
   ''' <param name="currentPlayer"></param>
   ''' <returns></returns>
   ''' <remarks></remarks>
   Public Function saveGame(ByVal owners() As Integer, ByVal values() As Integer, ByVal currentPlayer As PlayerNumber)

      Dim o As String = ArrayToString(owners)
      Dim v As String = ArrayToString(values)
      Dim c As Integer = currentPlayer

      Dim myCommand As New OleDbCommand

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "INSERT INTO Game ([Grid Owners], [Dice Values], [Current Player]) VALUES('" & o & "', '" & v & "', '" & c & "')"
      End With

      Try
         dbConn.Open()
      Catch ex As Exception
         makeConnection()
         dbConn.Open()
      End Try

      Try

         myCommand.ExecuteNonQuery()
         dbConn.Close()
         myCommand.Dispose()

      Catch Exp As OleDb.OleDbException

         MsgBox(Exp.GetType, MsgBoxStyle.Critical, "Oledb Error")
         Return False

      Catch Exp As Exception

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
         Return False

      End Try

      Return True

   End Function

   ''' <summary>
   ''' 
   ''' </summary>
   ''' <param name="number"></param>
   ''' <returns></returns>
   ''' <remarks></remarks>
   Public Function openGame(ByVal number As Integer) As GameGrid

      Dim myCommand As New OleDbCommand
      Dim game As GameGrid = Nothing
      Dim myReader As OleDbDataReader = Nothing

      With myCommand
         .Connection = dbConn
         .CommandType = CommandType.Text
         .CommandText = "SELECT [Game ID], [Grid Owners], [Dice Values], [Current Player] FROM Game WHERE ([Game ID] = " & number & ")"
      End With

      Try
         dbConn.Open()
      Catch ex As Exception
         makeConnection()
         dbConn.Open()
      End Try

      Try

         myReader = myCommand.ExecuteReader
         myReader.Read()

         game.UID = Convert.ToInt32(myReader(0))
         game.Owners = StringToArray(myReader(1), ",")
         game.Values = StringToArray(myReader(2), ",")
         game.CurrentPlayer = CType(Convert.ToInt32(myReader(3)), PlayerNumber)

      Catch Exp As OleDb.OleDbException

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "Oledb Error")

      Catch Exp As Exception

         MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")

      End Try

      dbConn.Close()
      myCommand.Dispose()
      myReader.Close()

      Return game

   End Function

End Module